package dragon.core.db;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import dragon.core.db.DBUtil.DatabaseProduct;
import dragon.core.exception.ExceptionHandler;
import dragon.core.page.Page;
import dragon.core.util.DateUtils;
import dragon.core.util.StringUtils;
import ejp.Database;
import ejp.DatabaseException;
import ejp.Result;

/**
 * 用户执行 select,insert,update,delete 等数据操纵语言
 */
public class DML {

	private static final Logger logger = LoggerFactory.getLogger(DML.class);

	/**
	 * 操作序列号的专用数据库对象，使用同一个数据库对象，可以保证序列的连贯
	 */
	private static Database seqopDB = null;

	/**
	 * EJP SQL 查询
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param sql
	 *            查询语句
	 * @return
	 * @throws ExceptionHandler
	 */
	private static Result<?> executeQuery(Database db, String sql) throws ExceptionHandler {
		try {
			return db.executeQuery(sql);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 判断指定的数据表是否存在
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param schemaname
	 *            表模式
	 * @param tableName
	 *            表名称
	 * @return
	 * @throws ExceptionHandler
	 */
	private static boolean isTableExist(Database db, String schemaname, String tableName) throws ExceptionHandler {
		boolean isExist = false;
		Result<?> res = null;

		DatabaseProduct product = DBUtil.getDatabaseProduct(db.getDatabaseName());
		String sql = null;
		if (DatabaseProduct.PostgreSQL.equals(product)) {
			sql = "select count(*)n from pg_tables where schemaname='" + schemaname.toLowerCase() + "' and tablename='" + tableName.toLowerCase() + "'";
		} else if (DatabaseProduct.Oracle.equals(product)) {
			sql = "select count(*)n from user_tables where table_name='" + tableName.toUpperCase() + "'";
		} else {
			// TODO:更多数据库类型支持
			return false;
		}

		try {
			res = executeQuery(db, sql);
			if (res != null && res.hasNext()) {
				while (res.next() != null) {
					isExist = ((Number) res.getColumnValue("N")).intValue() == 1;
				}
			}
		} catch (Exception e) {
			throw new ExceptionHandler(e);
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}

		sql = null;
		product = null;

		return isExist;
	}

	/**
	 * 获取记录集字段信息
	 * 
	 * @param res
	 *            记录集(ejp.Result)
	 * @return
	 * @throws SQLException
	 */
	private static ColumnMeta[] getColumns(Result<?> res) throws ExceptionHandler {
		if (res == null) {
			return null;
		}
		try {
			ResultSet rs = res.getStatement().getResultSet();
			return getColumns(rs);
		} catch (SQLException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 获取记录集字段信息
	 * 
	 * @param rs
	 *            记录集
	 * @return
	 * @throws SQLException
	 */
	public static ColumnMeta[] getColumns(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		ColumnMeta[] cols = new ColumnMeta[columnCount];
		for (int i = 1; i <= columnCount; i++) {
			String columnName = rsmd.getColumnName(i);// 字段名
			String columnLabel = rsmd.getColumnLabel(i);// 字段Label
			int columnType = rsmd.getColumnType(i);// 字段数据类型
			String columnTypeName = rsmd.getColumnTypeName(i);// 字段数据类型(文本)
			int columnDisplaySize = rsmd.getColumnDisplaySize(i);// 字段大小
			String columnClassName = rsmd.getColumnClassName(i);// 字段数据类型对应的Java类名
			int columnNullable = rsmd.isNullable(i);// 是否可空(必填项)columnNoNulls=0;columnNullable=1;columnNullableUnknown=2;
			cols[i - 1] = new ColumnMeta(columnName, columnLabel, columnType, columnTypeName, columnDisplaySize, columnClassName, columnNullable, null);
		}
		return cols;
	}

	/**
	 * 获取字段值
	 * 
	 * @param col
	 *            字段元信息
	 * @param res
	 *            记录集
	 * @return
	 * @throws DatabaseException
	 */
	private static Object getColumnValue(ColumnMeta col, Result<?> res) throws ExceptionHandler {
		if (col == null || res == null) {
			return null;
		}
		try {
			int columnType = col.getColumnType();
			String columnName = col.getColumnName();
			if (columnType == Types.NUMERIC) {
				return res.getColumnValue(columnName) != null ? ((BigDecimal) res.getColumnValue(columnName)).toString() : "";
			} else if (columnType == Types.VARCHAR) {
				return res.getColumnValue(columnName) != null ? res.getColumnValue(columnName).toString() : "";
			} else if (columnType == Types.CLOB) {
				Object clobObj = res.getColumnValue(columnName);
				if (clobObj != null) {
					Clob clob = (Clob) clobObj;
					StringBuilder content = new StringBuilder();
					Reader reader = null;
					BufferedReader bufferedReader = null;
					try {
						reader = clob.getCharacterStream();
						bufferedReader = new BufferedReader(reader);
						String s = null;
						while ((s = bufferedReader.readLine()) != null) {
							content.append(s);
						}
					} catch (SQLException e) {
						throw new ExceptionHandler(e);
					} catch (IOException e) {
						throw new ExceptionHandler(e);
					} finally {
						if (bufferedReader != null) {
							try {
								bufferedReader.close();
							} catch (IOException e) {
								logger.error(e.getMessage(), e);
							}
							bufferedReader = null;
						}
						if (reader != null) {
							try {
								reader.close();
							} catch (IOException e) {
								logger.error(e.getMessage(), e);
							}
							reader = null;
						}
					}
					return content.toString();
				}
			} else if (columnType == Types.DATE) {
				return DateUtils.format((Date) res.getColumnValue(columnName));
			} else if (columnType == Types.TIMESTAMP) {
				return DateUtils.format((Timestamp) res.getColumnValue(columnName), DateUtils.yyyy_MM_dd_HH_mm_ss_SSS);
			} else if (columnType == Types.TIME) {
				return DateUtils.format((Time) res.getColumnValue(columnName), DateUtils.HH_mm_ss);
			}
			// TODO:其它数据类型
			return res.getColumnValue(columnName);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 获取字段值
	 * 
	 * @param col
	 *            字段元信息
	 * @param rs
	 *            记录集
	 * @return
	 * @throws ExceptionHandler
	 */
	private static Object getColumnValue(ColumnMeta col, ResultSet rs) throws ExceptionHandler {
		if (col == null || rs == null) {
			return null;
		}
		try {
			int columnType = col.getColumnType();
			String columnName = col.getColumnName();

			Object value = rs.getObject(columnName);
			if (value == null) {
				return "";
			}

			if (columnType == Types.NUMERIC) {
				return rs.getBigDecimal(columnName);
			} else if (columnType == Types.VARCHAR) {
				return rs.getString(columnName);
			} else if (columnType == Types.CLOB) {
				Clob clob = rs.getClob(columnName);
				StringBuilder content = new StringBuilder();
				Reader reader = null;
				BufferedReader bufferedReader = null;
				try {
					reader = clob.getCharacterStream();
					bufferedReader = new BufferedReader(reader);
					String s = null;
					while ((s = bufferedReader.readLine()) != null) {
						content.append(s);
					}
				} catch (SQLException e) {
					throw new ExceptionHandler(e);
				} catch (IOException e) {
					throw new ExceptionHandler(e);
				} finally {
					if (bufferedReader != null) {
						try {
							bufferedReader.close();
						} catch (IOException e) {
							logger.error(e.getMessage(), e);
						}
						bufferedReader = null;
					}
					if (reader != null) {
						try {
							reader.close();
						} catch (IOException e) {
							logger.error(e.getMessage(), e);
						}
						reader = null;
					}
				}
				return content.toString();
			}
			return value;
		} catch (SQLException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 获取字段注释
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param tableName
	 *            表名称
	 * @return
	 * @throws ExceptionHandler
	 */
	private static Map<String, String> getColumnComments(Database db, String tableName) throws ExceptionHandler {
		Map<String, String> comments = new HashMap<String, String>();

		DatabaseProduct product = DBUtil.getDatabaseProduct(db.getDatabaseName());
		String sql = null;
		if (DatabaseProduct.PostgreSQL.equals(product)) {
			// TODO:完善
			return comments;
		} else if (DatabaseProduct.Oracle.equals(product)) {
			sql = "select column_name,comments from user_col_comments where table_name='" + tableName.toUpperCase() + "'";
		} else {
			// TODO:更多数据库类型支持
			return comments;
		}

		Result<?> res = null;
		try {
			res = executeQuery(db, sql);
			if (res != null && res.hasNext()) {
				while (res.next() != null) {
					Object comment = res.getColumnValue("COMMENTS");
					comments.put(res.getColumnValue("COLUMN_NAME").toString(), comment != null ? comment.toString() : "");
				}
			}
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}

		sql = null;
		product = null;

		return comments;
	}

	/**
	 * 查询数据表元信息(连接默认的数据库) *@param schemaname 表模式
	 * 
	 * @param tableName
	 *            数据表名称
	 * @param pk
	 *            主键，可以是多个
	 * @return
	 * @throws ExceptionHandler
	 */
	public static TableMeta getTableMeta(String schemaname, String tableName, String... pk) throws ExceptionHandler {
		return getTableMeta("", schemaname, tableName, pk);
	}

	/**
	 * 查询数据表元信息
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param schemaname
	 *            表模式
	 * @param tableName
	 *            数据表名称
	 * @param pk
	 *            主键，可以是多个
	 * @return
	 * @throws ExceptionHandler
	 */
	public static TableMeta getTableMeta(String dbName, String schemaname, String tableName, String... pk) throws ExceptionHandler {
		return getTableMeta(DBUtil.getDatabase(dbName), schemaname, tableName, pk);
	}

	/**
	 * 查询数据表元信息
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param schemaname
	 *            表模式
	 * @param tableName
	 *            数据表名称
	 * @param pk
	 *            主键，可以是多个
	 * @return
	 * @throws ExceptionHandler
	 */
	public static TableMeta getTableMeta(Database db, String schemaname, String tableName, String... pk) throws ExceptionHandler {

		// 将表名转换为大写
		tableName = tableName.toUpperCase();

		// 判断指定的表是否存在
		boolean isTableExist = isTableExist(db, schemaname, tableName);
		if (logger.isDebugEnabled()) {
			logger.debug(tableName + " 是否存在：" + isTableExist);
		}
		if (!isTableExist) {
			return null;
		}

		if (logger.isDebugEnabled()) {
			logger.debug("pk:" + pk);
		}
		StringBuilder sql = new StringBuilder("select * from ").append(schemaname != null ? schemaname + "." : "").append(tableName);
		if (pk != null && pk.length > 0) {
			sql.append(" where");
			for (String s : pk) {
				sql.append(" ").append(s).append("=null and");
			}
		} else {
			sql.append(" where id=null");
		}

		String sqlSelect = sql.toString();
		if (sqlSelect.endsWith("and")) {
			sqlSelect = sqlSelect.substring(0, sqlSelect.length() - 3);
		}

		if (logger.isDebugEnabled()) {
			logger.debug("sql:" + sqlSelect);
		}

		Result<?> res = null;
		try {
			TableMeta table = new TableMeta();
			table.setName(tableName);

			// 获取表注释
			DatabaseProduct product = DBUtil.getDatabaseProduct(db.getDatabaseName());
			if (DatabaseProduct.PostgreSQL.equals(product)) {
				// TODO:完善
			} else if (DatabaseProduct.Oracle.equals(product)) {
				res = executeQuery(db, "select comments from user_tab_comments where table_name='" + tableName + "'");
				if (res != null && res.hasNext()) {
					while (res.next() != null) {
						Object comments = null;
						try {
							comments = res.getColumnValue("COMMENTS");
						} catch (DatabaseException e) {
							throw new ExceptionHandler(e);
						}
						table.setComment(comments != null ? comments.toString() : "");
					}
				}
			} else {
				// TODO:更多数据库类型支持
			}
			product = null;

			// 获取字段元信息
			res = executeQuery(db, sqlSelect);
			ColumnMeta[] cols = getColumns(res);

			// 获取字段注释
			Map<String, String> comments = getColumnComments(db, tableName);
			if (cols != null) {
				for (ColumnMeta col : cols) {
					col.setComment(comments.get(col.getColumnName()));
				}
			}
			table.setColumns(cols);

			return table;
		} catch (ExceptionHandler e) {
			throw new ExceptionHandler(e);
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}
	}

	/**
	 * 分页查找(连接默认的数据库)
	 * 
	 * @param page
	 *            分页参数
	 * @throws ExceptionHandler
	 */
	public static void query(Page page) throws ExceptionHandler {
		query("", page);
	}

	/**
	 * 分页查找
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param page
	 *            分页参数 &lt;class:dragon.core.page.Page&gt;
	 * @throws ExceptionHandler
	 */
	public static void query(String dbName, Page page) throws ExceptionHandler {
		query(DBUtil.getDatabase(dbName), page);
	}

	/**
	 * 分页查找
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param page
	 *            分页参数 &lt;class:dragon.core.page.Page&gt;
	 * @throws ExceptionHandler
	 */
	public static void query(Database db, Page page) throws ExceptionHandler {

		String sql = page.getSql();

		DatabaseProduct product = DBUtil.getDatabaseProduct(db.getDatabaseName());

		// Step1 计算数据总数
		// TODO:需要优化算法，将各数据表的数据总量缓存，在数据表不发生变更的情况下不再查询
		String countSQL = "select count(*)C from(" + sql + ")as base";
		Result<?> res = null;
		try {
			res = executeQuery(db, countSQL);
			int totalItems = 0;
			if (res != null && res.hasNext()) {
				while (res.next() != null) {
					if (DatabaseProduct.PostgreSQL.equals(product)) {
						totalItems = ((Long) res.getColumnValue("C")).intValue();
					} else if (DatabaseProduct.Oracle.equals(product)) {
						totalItems = ((BigDecimal) res.getColumnValue("C")).intValue();
					}
				}
			}
			page.setTotalItems(totalItems);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}

		// Step2 计算记录集索引， 这一步很重要，必须要调用
		page.calculateIndex();

		// Step3 查询记录集
		try {
			if (DatabaseProduct.PostgreSQL.equals(product)) {
				res = executeQuery(db, "select * from (" + sql + ") as temp limit " + page.getItemsPerPage() + " offset " + page.getFirstIndex());
			}
			// TODO更多数据库类型
			if (res != null) {
				// 解析字段元信息
				ColumnMeta[] cols = getColumns(res);
				page.setCols(cols);// 保存字段元信息

				List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(page.getItemsPerPage());
				Map<String, Object> currentRow = null;
				Object value = null;
				while (res.next() != null) {
					currentRow = new HashMap<String, Object>();
					for (int i = 0; i < cols.length; i++) {
						value = getColumnValue(cols[i], res);
						currentRow.put(cols[i].getColumnName().toUpperCase(), value);
						currentRow.put(String.valueOf(i), value);
					}
					result.add(currentRow);
				}
				page.setResult(result);

				value = null;
				currentRow = null;
				result = null;
				cols = null;

			}
		} catch (ExceptionHandler e) {
			throw e;
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}

		product = null;
		sql = null;

	}

	/**
	 * 数据查询，不带分页(连接默认的数据库)
	 * 
	 * @param sql
	 *            查询语句
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> query(String sql) throws ExceptionHandler {
		return query("", sql);
	}

	/**
	 * 数据查询，不带分页
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param sql
	 *            查询语句
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> query(String dbName, String sql) throws ExceptionHandler {
		return query(DBUtil.getDatabase(dbName), sql);
	}

	/**
	 * 数据查询，不带分页
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param sql
	 *            查询语句
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> query(Database db, String sql) throws ExceptionHandler {

		Result<?> res = null;
		try {
			res = executeQuery(db, sql);
			ColumnMeta[] cols = getColumns(res);

			// 取得所需数据
			List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
			if (res != null && res.hasNext()) {
				while (res.next() != null) {
					Map<String, Object> currentRow = new HashMap<String, Object>();
					for (int i = 0; i < cols.length; i++) {
						Object value = getColumnValue(cols[i], res);
						currentRow.put(cols[i].getColumnName().toUpperCase(), value);
						currentRow.put(String.valueOf(i), value);
					}
					result.add(currentRow);
				}
			}
			return result;
		} finally {
			if (res != null) {
				res.close();
				res = null;
			}
		}
	}

	/**
	 * ejp原始查询，将查询结果封装成指定类别的集合(连接默认的数据库)
	 * 
	 * @param c
	 *            集合
	 * @param cs
	 *            数据包对应的POJO类
	 * @param sql
	 *            查询语句
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T query(Collection<?> c, Class<?> cs, String sql) throws ExceptionHandler {
		return (T) query(null, c, cs, sql);
	}

	/**
	 * ejp原始查询，将查询结果封装成指定类别的集合
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param c
	 *            集合
	 * @param cs
	 *            数据包对应的POJO类
	 * @param sql
	 *            查询语句
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T query(String dbName, Collection<?> c, Class<?> cs, String sql) throws ExceptionHandler {
		try {
			return (T) DBUtil.getDatabaseManager(dbName).executeQuery(c.getClass().newInstance(), cs, sql);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} catch (InstantiationException e) {
			throw new ExceptionHandler(e);
		} catch (IllegalAccessException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 加载对象(根据类型对应的表名自动判断需要连接的数据库)
	 * 
	 * @param cs
	 *            指定类型
	 * @param externalClauses
	 *            条件语句
	 * @param args
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T loadObject(Class<?> cs, String externalClauses, Object... args) throws ExceptionHandler {
		try {
			// 通过反射获取表模式
			String defaultDbName = DBUtil.defaultDbName;
			try {
				Field field = cs.getDeclaredField("tableName");
				String tableName = (String) field.get(null);
				if (!StringUtils.isEmpty(tableName)) {
					defaultDbName = tableName.substring(0, tableName.indexOf("."));
				}
				tableName = null;
				field = null;
			} catch (SecurityException e) {
			} catch (NoSuchFieldException e) {
			} catch (IllegalArgumentException e) {
			} catch (IllegalAccessException e) {
			}

			return (T) DBUtil.getDatabase(defaultDbName).loadObject(cs, externalClauses, args);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} catch (ExceptionHandler e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 加载对象
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param cs
	 *            指定类型
	 * @param externalClauses
	 *            条件语句
	 * @param args
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static <T> T loadObject(String dbName, Class<?> cs, String externalClauses, Object... args) throws ExceptionHandler {
		return loadObject(DBUtil.getDatabase(dbName), cs, externalClauses, args);
	}

	/**
	 * 加载对象
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param cs
	 *            指定类型
	 * @param externalClauses
	 *            条件语句
	 * @param args
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T loadObject(Database db, Class<?> cs, String externalClauses, Object... args) throws ExceptionHandler {
		try {
			return (T) db.loadObject(cs, externalClauses, args);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 加载指定类型数据(根据类型对应的表名自动判断需要连接的数据库)
	 * 
	 * @param c
	 *            集合
	 * @param cs
	 *            指定类型
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T loadObjects(Collection<?> c, Class<?> cs) throws ExceptionHandler {
		try {
			// 通过反射获取表模式
			String defaultDbName = DBUtil.defaultDbName;
			try {
				Field field = cs.getDeclaredField("tableName");
				String tableName = (String) field.get(null);
				if (!StringUtils.isEmpty(tableName)) {
					defaultDbName = tableName.substring(0, tableName.indexOf("."));
				}
				tableName = null;
				field = null;
			} catch (SecurityException e) {
			} catch (NoSuchFieldException e) {
			} catch (IllegalArgumentException e) {
			} catch (IllegalAccessException e) {
			}

			return (T) DBUtil.getDatabase(defaultDbName).loadObjects(c.getClass().newInstance(), cs);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} catch (InstantiationException e) {
			throw new ExceptionHandler(e);
		} catch (IllegalAccessException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 加载指定类型数据
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param c
	 *            集合
	 * @param cs
	 *            指定类型
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T loadObjects(String dbName, Collection<?> c, Class<?> cs) throws ExceptionHandler {
		return (T) loadObjects(DBUtil.getDatabase(dbName), c, cs);
	}

	/**
	 * 加载指定类型数据
	 * 
	 * @param db
	 *            指定连接的数据库对象
	 * @param c
	 *            集合
	 * @param cs
	 *            指定类型
	 * @return
	 * @throws ExceptionHandler
	 */
	@SuppressWarnings("unchecked")
	public static <T> T loadObjects(Database db, Collection<?> c, Class<?> cs) throws ExceptionHandler {
		try {
			return (T) db.loadObjects(c.getClass().newInstance(), cs);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} catch (InstantiationException e) {
			throw new ExceptionHandler(e);
		} catch (IllegalAccessException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 事务开始
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @throws ExceptionHandler
	 */
	public static void beginTransaction(Database db) throws ExceptionHandler {
		try {
			db.beginTransaction();
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 事务结束
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @throws ExceptionHandler
	 */
	public static void endTransaction(Database db) throws ExceptionHandler {
		try {
			db.endTransaction();
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 回滚事务
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @throws ExceptionHandler
	 */
	public static void rollback(Database db) throws ExceptionHandler {
		try {
			db.rollback();
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 保存或更新对象(根据对象类型对应的表名自动判断需要连接的数据库)
	 * 
	 * @param object
	 *            待提交对象
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int saveOrUpdateObject(Object object) throws ExceptionHandler {
		try {
			// 通过反射获取表模式
			String defaultDbName = DBUtil.defaultDbName;
			try {
				Field field = object.getClass().getDeclaredField("tableName");
				String tableName = (String) field.get(null);
				if (!StringUtils.isEmpty(tableName)) {
					defaultDbName = tableName.substring(0, tableName.indexOf("."));
				}
				tableName = null;
				field = null;
			} catch (SecurityException e) {
			} catch (NoSuchFieldException e) {
			} catch (IllegalArgumentException e) {
			} catch (IllegalAccessException e) {
			}

			return DBUtil.getDatabase(defaultDbName).saveObject(object);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		} catch (ExceptionHandler e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 保存或更新数据
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param object
	 *            待提交对象
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int saveOrUpdateObject(String dbName, Object object) throws ExceptionHandler {
		return saveOrUpdateObject(DBUtil.getDatabase(dbName), object);
	}

	/**
	 * 保存或更新数据
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @param object
	 *            待提交对象
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int saveOrUpdateObject(Database db, Object object) throws ExceptionHandler {
		try {
			return db.saveObject(object);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 执行SQL语句(连接默认的数据库)
	 * 
	 * @param sql
	 *            SQL语句
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int executeUpdate(String sql) throws ExceptionHandler {
		return executeUpdate("", sql);
	}

	/**
	 * 执行SQL语句
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param sql
	 *            SQL语句
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int executeUpdate(String dbName, String sql) throws ExceptionHandler {
		return executeUpdate(DBUtil.getDatabase(dbName), sql);
	}

	/**
	 * 执行SQL语句
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @param sql
	 *            SQL语句
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int executeUpdate(Database db, String sql) throws ExceptionHandler {
		try {
			return db.executeUpdate(sql);
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
	}

	/**
	 * 预处理方式执行删除或更新(连接默认的数据库)
	 * 
	 * @param sql
	 *            更新或删除语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int prepareExecuteUpdate(String sql, Object... parameters) throws ExceptionHandler {
		return prepareExecuteUpdate("", sql, parameters);
	}

	/**
	 * 预处理方式执行删除或更新
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param sql
	 *            更新或删除语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int prepareExecuteUpdate(String dbName, String sql, Object... parameters) throws ExceptionHandler {
		return prepareExecuteUpdate(DBUtil.getDatabase(dbName), sql, parameters);
	}

	/**
	 * 预处理方式执行删除或更新
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @param sql
	 *            更新或删除语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int prepareExecuteUpdate(Database db, String sql, Object... parameters) throws ExceptionHandler {
		PreparedStatement ps = null;
		try {
			ps = db.getConnection().prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new ExceptionHandler(e);
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					logger.error(e.getMessage(), e);
				}
				ps = null;
			}
		}
	}

	/**
	 * 预处理方式查询数据(连接默认的数据库)
	 * 
	 * @param sql
	 *            查询语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> prepareQuery(String sql, Object... parameters) throws ExceptionHandler {
		return prepareQuery("", sql, parameters);
	}

	/**
	 * 预处理方式查询数据
	 * 
	 * @param dbName
	 *            指定连接的数据库名称
	 * @param sql
	 *            查询语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> prepareQuery(String dbName, String sql, Object... parameters) throws ExceptionHandler {
		return prepareQuery(DBUtil.getDatabase(dbName), sql, parameters);
	}

	/**
	 * 预处理方式查询数据
	 * 
	 * @param db
	 *            指定连接的数据库
	 * @param sql
	 *            查询语句
	 * @param parameters
	 *            参数
	 * @return
	 * @throws ExceptionHandler
	 */
	public static List<Map<String, Object>> prepareQuery(Database db, String sql, Object... parameters) throws ExceptionHandler {
		PreparedStatement ps = null;
		try {
			ps = db.getConnection().prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			ResultSet rs = ps.executeQuery();
			ColumnMeta[] cms = getColumns(rs);
			List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (ColumnMeta cm : cms) {
					map.put(cm.getColumnName().toUpperCase(), getColumnValue(cm, rs));
				}
				result.add(map);
			}
			return result;
		} catch (SQLException e) {
			throw new ExceptionHandler(e);
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					logger.error(e.getMessage(), e);
				}
				ps = null;
			}
		}
	}

	/**
	 * 获取序列值
	 * 
	 * @param sequencename
	 *            序列名称
	 * @return
	 * @throws ExceptionHandler
	 */
	public static int getSequenceNextVal(String sequencename) throws ExceptionHandler {

		if (seqopDB == null || seqopDB.isClosed()) {
			seqopDB = DBUtil.getDatabase();
		}

		int result = -1;
		try {
			Result<?> res = executeQuery(seqopDB, "select nextval('" + sequencename + "')nextval");
			if (res != null && res.hasNext()) {
				while (res.next() != null) {
					result = Integer.parseInt(res.getColumnValue("NEXTVAL").toString());
				}
			}
			res = null;
		} catch (DatabaseException e) {
			throw new ExceptionHandler(e);
		}
		return result;
	}
}
